import json

import mysql.connector
import pandas as pd

# 读取Excel文件
file_path = 'D:\\工作文档\\多诊合参逻辑\\头面红外合参\\头面证候.xlsx'
df = pd.read_excel(file_path, header=0, engine='openpyxl')

# 显示前5行数据
json_data = df.to_json(orient='records')
array = json.loads(json_data)
for item in array:
    # 插入数据的SQL语句
    sql = "INSERT INTO syndrome_element_dict (name, code, elements, elements_code, elements_order, elements_score, status, type) VALUES ('{}', '{}', `{}`, `{}`,'{}', '{}',{}, {});".format(
        item["name"], item['code'], json.loads(item['elements']),
        json.loads(item['elements_code']),
        json.loads(item['elements_order']), json.loads(item['elements_score']), item['status'],
        item['type'])
    print(sql.replace("'", '"'))
# 创建连接
# connection = mysql.connector.connect(
#     host="10.11.2.55",
#     user="root",
#     password="Yzy@algo",
#     database="hawkeye_algorithm"
# )
#
# 创建游标
# cursor = connection.cursor()
#
# for item in array:
#     # 插入数据的SQL语句
#     sql = "INSERT INTO syndrome_element_dict (name, code, elements, elements_code, elements_order, elements_score, status, type) VALUES (%s, %s, %s, %s,%s, %s,%s, %s)"
#     values = (item['name'], item['code'], json.loads(item['elements']), json.loads(item['elements_code']),
#               json.loads(item['elements_order']), json.loads(item['elements_score']), item['status'],
#               item['type'])
#     # 执行SQL语句
#     cursor.execute(sql, values)
#     # 提交事务
#     connection.commit()
# # 关闭游标和连接
# cursor.close()
# connection.close()
